package com.braveo.dict.creator.common;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashMap;

public class DictDbSQLite3DAO implements DictDb {
    static final String TABLE_IMAGE = "image";

	static final String TABLE_ANDROID_METADATA = "android_metadata";

	static final String TABLE_DICT = "dict";

	public static final String [] DATABASE_CREATION = {
        "CREATE TABLE dict ( _id INTEGER PRIMARY KEY AUTOINCREMENT, Word TEXT NOT NULL, EXPLANATION TEXT NOT NULL )",
        "CREATE INDEX word_index ON dict (Word)",
        "CREATE TABLE android_metadata (locale TEXT)",
        "INSERT INTO android_metadata(locale) VALUES('en_US')",
        "CREATE TABLE image ( _id INTEGER PRIMARY KEY AUTOINCREMENT, " +
				"wordId INTEGER NOT NULL, " +
				"imageTitle TEXT, " +
				"originalFileName TEXT NOT NULL, " +
				"mimeType TEXT NOT NULL, " +
				"imageData BLOG);",
		"CREATE INDEX image_word_index ON image (wordId);",        
        
    };
	
	private final File dbFile;
	private boolean initialized = false;
	private Connection connection;
	
	public DictDbSQLite3DAO(File dbFile) {
		this.dbFile = dbFile;
	}
	
	private void init() {
		if(!initialized) {
			try {
				Class.forName("org.sqlite.JDBC");
			} catch(ClassNotFoundException ex) {
				throw new RuntimeException(ex);
			}
			
			initialized = true;
		}
	}

	boolean checkTableByName(String tableName) throws SQLException {
		String sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=?";
		PreparedStatement pstat = connection.prepareStatement(sql);
		pstat.setString(1, tableName);
		ResultSet rs = pstat.executeQuery();
		boolean result = false;
		if(rs.next())
			result = true;
		rs.close();
		pstat.close();
		return result;
	}
	
	void executeUpdate(String sql) throws SQLException {
		Statement stat = connection.createStatement();
		stat.executeUpdate(sql);
		stat.close();
	}
	
	public void openDatabase() throws SQLException {
		init();
		
		if(connection != null) 
			throw new IllegalStateException();
		
		dbFile.getAbsoluteFile().getParentFile().mkdirs();
		connection = DriverManager.getConnection("jdbc:sqlite:" + dbFile.getPath());
		
		boolean dictTable = checkTableByName(TABLE_DICT);
		boolean metaTable = checkTableByName(TABLE_ANDROID_METADATA);
		boolean imageTable = checkTableByName(TABLE_IMAGE);
		
		boolean all = dictTable && metaTable && imageTable;
		boolean none = !(dictTable || metaTable || imageTable);
		
		if(all)
			return;
		
		if(none) {
			for(String sql : DATABASE_CREATION)
				executeUpdate(sql);
					
			System.out.println("Create new database");
			return;
		}
		
		throw new RuntimeException("Tables partially exist");		
	}
	
	public void closeDatabase() throws SQLException {
		connection.close();
		connection = null;
	}

	public void addOneWord(WordEntry entry) throws SQLException {
		int wordId = findWordIdByWord(entry.getWord());
		
		
		if(wordId < 0) {
			PreparedStatement pstat = connection.prepareStatement("INSERT INTO dict(word, EXPLANATION)" +
				" VALUES(?, ?)");
		
			pstat.setString(1, entry.getWord());
			pstat.setString(2, entry.getExplanation());

			pstat.execute();

			pstat.close();
			wordId = findWordIdByWord(entry.getWord());
		} else {
			PreparedStatement pstat = connection.prepareStatement("UPDATE dict set word=?, " +
					"explanation=? WHERE _id=?" );
			pstat.setString(1, entry.getWord());
			pstat.setString(2, entry.getExplanation());
			pstat.setInt(3, wordId);
			pstat.executeUpdate();
			pstat.close();
			
			pstat = connection.prepareStatement("DELETE FROM image WHERE wordId=?");
			pstat.setInt(1, wordId);
			pstat.executeUpdate();
			pstat.close();
		}
		
		if(entry.getImageList() != null && !entry.getImageList().isEmpty()) {
			PreparedStatement pstatImage = connection
					.prepareStatement("INSERT INTO image(wordId, imageTitle, originalFileName, mimeType, imageData)" +
							" VALUES(?, ?, ?, ?, ?)");

			for(ImageEntry ie : entry.getImageList()) {
				pstatImage.setInt(1, wordId);
				pstatImage.setString(2, new File(ie.getFileName()).getName());
				pstatImage.setString(3, new File(ie.getFileName()).getName());
				pstatImage.setString(4, ie.getMimeType());
				pstatImage.setBytes(5, ie.getImageData());
				pstatImage.addBatch();
			}
			
			connection.setAutoCommit(false);
			pstatImage.executeBatch();
			connection.setAutoCommit(true);
			pstatImage.close();
		}
	}
	
	//@Override
	public int findWordIdByWord(String word) throws SQLException {
		PreparedStatement pstat;
		pstat = connection.prepareStatement("SELECT _id FROM dict WHERE word=?");
		pstat.setString(1, word);
		ResultSet rs = pstat.executeQuery();
		int wordId = -1;
		if (rs.next()) {
			wordId = rs.getInt(1);
		}
		rs.close();
		pstat.close();
		return wordId;
	}

	//@Override
	public void addWords(Collection<? extends WordEntry> words) throws SQLException {
		PreparedStatement pstat = connection.prepareStatement("INSERT INTO dict(word, EXPLANATION)" +
				" VALUES(?, ?)");
		
		
		for(WordEntry entry : words) {		
			pstat.setString(1, entry.getWord());
			pstat.setString(2, entry.getExplanation());
			pstat.addBatch();
		}
		
		connection.setAutoCommit(false);
		pstat.executeBatch();
		connection.setAutoCommit(true);
		
		HashMap<String, Integer> wordIdMap = new HashMap<String, Integer>();
		pstat = connection.prepareStatement("SELECT _id, word FROM dict");
		ResultSet rs = pstat.executeQuery();
		if(rs.next()) {
			do {
				wordIdMap.put(rs.getString(2), rs.getInt(1));
			} while(rs.next());
		}
		
		rs.close();
		pstat.close();

		PreparedStatement pstatImage = connection
				.prepareStatement("INSERT INTO image(wordId, imageTitle, originalFileName, mimeType, imageData)" +
						" VALUES(?, ?, ?, ?, ?)");
		
		for(WordEntry entry : words) {

			for(ImageEntry ie : entry.getImageList()) {
				pstatImage.setInt(1, wordIdMap.get(entry.getWord()));
				pstatImage.setString(2, new File(ie.getFileName()).getName());
				pstatImage.setString(3, new File(ie.getFileName()).getName());
				pstatImage.setString(4, ie.getMimeType());
				pstatImage.setBytes(5, ie.getImageData());
				pstatImage.addBatch();
			}
			
		}

		connection.setAutoCommit(false);
		pstatImage.executeBatch();
		connection.setAutoCommit(true);
		pstatImage.close();
	}

}
